package com.sky.service.Report;

import com.sky.dto.GoodSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.Workspace.WorkspaceService;
import com.sky.vo.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URL;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Author 褚金龙
 * @Date 2024/10/15 21:55
 */
@Service
public class ReportServiceImpl implements ReportService{
     @Autowired
     private OrderMapper orderMapper;

     @Autowired
     private UserMapper userMapper;


     @Autowired
     private WorkspaceService workspaceService;
    /**
     * 统计指定时区内的营业额统计
     * @param begin
     * @param end
     * @return
     */

    @Override
    public TurnoverVO getTurnoverStatistics(LocalDate begin, LocalDate end) {
     // 当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> dateList=new ArrayList<>();

        dateList.add(begin);
        while (!begin.equals(end)){
            //日期计算，计算指定日期的后一天的日期
            begin = begin.plusDays(1);
            dateList.add(begin);
        }
      //存放每天的营业额
        List<Double> turnoverList=new ArrayList<>();


            for (LocalDate date : dateList) {
                // 查询date日期对应的营业额数据，营业额是指：状态为"已完成"的订单
                LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
                LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

                Map map=new HashMap();
                map.put("beginTime",beginTime);
                map.put("endTime",endTime);
                map.put("status",Orders.COMPLETED);

                Double turnover=  orderMapper.sumByMap(map);
                turnover=turnover==null?0.0:turnover;
                turnoverList.add(turnover);

            }




        return TurnoverVO.builder().
                dateList(StringUtils.join(dateList, ","))
                .turnoverList(StringUtils.join(turnoverList,","))
                .build()
                ;
    }


    /**
     * 用户数据统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserCountVO getUserCount(LocalDate begin, LocalDate end) {
        // 当前集合用于存放从begin到end范围内的每天的日期

        List<LocalDate> dateList=new ArrayList<>();
        dateList.add(begin);
        while (!begin.equals(end)){
            //日期计算，计算指定日期的后一天的日期
            begin = begin.plusDays(1);
            dateList.add(begin);
        }

        // 每天新增用户：主要依据用户注册时间来统计
        List<Integer> newUserList=new ArrayList<>();
        // 每天的用户总数
        List<Integer> totalList=new ArrayList<>();
        for (LocalDate date : dateList) {
            // 查询date日期对应的营业额数据，营业额是指：状态为"已完成"的订单
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            Map map=new HashMap();

            map.put("endTime",endTime);
            Integer totalCount= userMapper.countByMap(map); //每一天的用户总数
            totalList.add(totalCount);
            map.put("beginTime",beginTime);
           Integer newUserCount= userMapper.countByMap(map); //获取每一天新增的用户总数

            newUserList.add(newUserCount);
        }

        return UserCountVO.builder()
                .dateList(StringUtils.join(dateList,","))
                .newUserList(StringUtils.join(newUserList,","))
                .totalUserList(StringUtils.join(totalList,","))
                .build()  ;
    }


    /**
     * 订单数据统计
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderCountVO getOrderCount(LocalDate begin, LocalDate end) {
        // 1.当前集合用于存放从begin到end范围内的每天的日期
        List<LocalDate> dateList=new ArrayList<>();

        dateList.add(begin);
        while (!begin.equals(end)){
            //日期计算，计算指定日期的后一天的日期
            begin = begin.plusDays(1);
            dateList.add(begin);
        }

        //存放每天的订单总数
        List<Integer> orderCountList=new ArrayList<>();
        //存放每天的有效订单数
        List<Integer> validOrderCountList=new ArrayList<>();
        for (LocalDate date : dateList) {
            // 查询date日期对应的营业额数据，营业额是指：状态为"已完成"的订单
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            // 1.查询每天的订单总数列表 select count(id) from orders where order_time &gt;  #{beginTime} and order_time &lt; #{endTime}
            Integer orderCount = getOrderCount(beginTime, endTime, null);
            orderCountList.add(orderCount);
            //2.查询每天的有效订单数列表-已完成的 select count(id) from orders where order_time &gt;  #{beginTime} and order_time &lt; #{endTime} and status=#{5}
            Integer completedOrderCount = getOrderCount(beginTime, endTime, Orders.COMPLETED);

            validOrderCountList.add(completedOrderCount);
        }

        //计算时间区间内的订单总数量
        Integer totalOrderCount = orderCountList.stream().reduce(Integer::sum).get();
        //计算时间区间内的有效订单数
        Integer validOrderCount= validOrderCountList.stream().reduce(Integer::sum).get();

         //2.订单完成率  有效订单数/订单总数量
        Double orderCompletionRate=0.0;
        if(totalOrderCount!=0){
            orderCompletionRate = validOrderCount.doubleValue() / totalOrderCount;
        }



        return OrderCountVO
                .builder()
                .dateList(StringUtils.join(dateList,","))
                 .orderCountList(StringUtils.join(orderCountList,","))
                .totalOrderCount(totalOrderCount)
                .validOrderCountList(StringUtils.join(validOrderCountList,","))
                .validOrderCount(validOrderCount)
                .orderCompletionRate(orderCompletionRate)
                .build()
                ;
    }

   public Integer getOrderCount(LocalDateTime beginTime, LocalDateTime endTime,Integer status){
       // 统计订单数据
        Map map=new HashMap();
        map.put("beginTime",beginTime);
        map.put("endTime",endTime);
        map.put("status",status);

        Integer result = orderMapper.countByMap(map);
        return result ;
    }






    /**
     * 统计时间区间内销量排名前10
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO getSalesTop10(LocalDate begin, LocalDate end) {
        // 1.当前集合用于存放从begin到end范围内的每天的日期
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);
            Map map=new HashMap();
            map.put("beginTime",beginTime);
            map.put("endTime",endTime);
            map.put("status",Orders.COMPLETED);

         List<GoodSalesDTO> goodSalesDTO=   orderMapper.getSalesTop10(map);
        /* // 菜品名称
         List<String> nameList=new ArrayList<>();
         // 购买份数
        List<Integer> numberList=new ArrayList<>();*/
        /*for (GoodSalesDTO salesDTO : goodSalesDTO) {
            nameList.add(salesDTO.getName());
            numberList.add(salesDTO.getNumber());
        }*/
        //第二种方法
        List<String> nameList = goodSalesDTO.stream().map(GoodSalesDTO::getName).collect(Collectors.toList());
        List<Integer> numberList = goodSalesDTO.stream().map(GoodSalesDTO::getNumber).collect(Collectors.toList());


        return SalesTop10ReportVO.builder()
                .numberList(StringUtils.join(numberList,","))
                .nameList(StringUtils.join(nameList,","))
                .build()
                ;
    }

    /**
     * 导出运营数据报表
     */
    @Override
    public void getExport(HttpServletResponse response) throws IOException {
        // 1.查询数据库获取营业数据   --查询最近30天的运营数据
            //1.1 获取概览数据
        LocalDate dateBegin = LocalDate.now().minusDays(30);//30天前
        LocalDateTime begin = LocalDateTime.of(dateBegin, LocalTime.MIN);
        LocalDate dateEnd = LocalDate.now().minusDays(1); //昨天
        LocalDateTime end = LocalDateTime.of(dateEnd, LocalTime.MAX);
        BusinessVO businessVO= workspaceService.getTodayData(begin,end);






        //2.通过POI将数据写入到excel中
        //从当前类路径下读取资源
        InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        // 基于已有文件创建excel文件
        XSSFSheet sheet = null;
        XSSFWorkbook excel=null;
        try {
            // 2.1 填入概览数据
             excel = new XSSFWorkbook(resourceAsStream);



            //通过页名查询
            sheet = excel.getSheet("Sheet1");
            DecimalFormat df=new DecimalFormat("0.00");
            //填充数据-时间
            sheet.getRow(2).getCell(0).setCellValue("时间："+dateBegin+"至"+dateEnd);
            //填充数据-概览数据
            //填充数据-营业额

            sheet.getRow(4).getCell(1).setCellValue(df.format(businessVO.getTurnover().doubleValue()) );
            //填充数据-订单完成率
            sheet.getRow(4).getCell(3).setCellValue(df.format(businessVO.getOrderCompletionRate().doubleValue()) );
            //填充数据-新增用户数
            sheet.getRow(4).getCell(5).setCellValue(businessVO.getNewUsers());
            //填充数据-有效订单
            sheet.getRow(5).getCell(1).setCellValue(businessVO.getValidOrder());
            //填充数据-平均客单价
            sheet.getRow(5).getCell(3).setCellValue(df.format(businessVO.getUnitPrice().doubleValue()) );



            //2.1 填入明细数据
            // 查询某一天的营业数据

            for (int i = 0; i < 30; i++) {
                LocalDate date=dateBegin.plusDays(i);
                //查询某一天的营业数据
                BusinessVO businessData=workspaceService.getTodayData(LocalDateTime.of(date,LocalTime.MIN),end);
                //获得某一行
                XSSFRow row = sheet.getRow(8 + i);
               row.getCell(0).setCellValue(date.toString());
               row.getCell(1).setCellValue(df.format(businessData.getTurnover().doubleValue()));
               row.getCell(2).setCellValue(businessData.getValidOrder());
               row.getCell(3).setCellValue(df.format(businessData.getOrderCompletionRate().doubleValue()));
               row.getCell(4).setCellValue(df.format(businessData.getUnitPrice().doubleValue()));
               row.getCell(5).setCellValue(businessData.getNewUsers());

            }
            // 创建样式 - 字体居中对齐
            CellStyle style = excel.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

          //3.通过输出流将Excel文件下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);

            out.close();
            excel.close();
        } catch (Exception e) {
           e.printStackTrace();
        }












    }
}
